package com.java.ttt.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.java.ttt.bo.GameRoom;
import com.java.ttt.bo.User;

public class WelcomeDao{

	public User getuserDetails(String userName, String password) {

		User user = null;
		Connection conn = null;
		ResultSet rs = null; 
		PreparedStatement ps = null;

		try {
			conn = BaseDbConnection.getConnection();

			String sql = "SELECT Id, name, username FROM ttt3.User WHERE ";
			sql += " username = ? AND password = ?";
			
			ps = conn.prepareStatement(sql);

			ps.setString(1, userName);
			ps.setString(2, password);
			rs = ps.executeQuery();

			while (rs.next()) {
				user = new User();
				
				user.setUserId(rs.getLong("Id"));
				user.setUsername(rs.getString("username"));
				user.setName(rs.getString("name"));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			/*try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}*/
			
		}

		return user;
	}
	
	public ArrayList<GameRoom> getOpenGameRoomsList () {
		
		ArrayList<GameRoom> gameRooms = new ArrayList<GameRoom>();
		Connection conn = null;
		ResultSet rs = null; 
		PreparedStatement ps = null;

		try {
			conn = BaseDbConnection.getConnection();

			String query = "SELECT g1.id gameId, u1.Id palyer1_id, u1.Name palyer1_name, u1.username palyer1_username, u2.Id palyer2_id, u2.Name palyer2_name, u2.username palyer2_username"
					+ " FROM ttt3.ActiveGames g1 "
					+ " LEFT JOIN ttt3.User u1 "
					+ " ON u1.Id = g1.Player1 "
					+ " LEFT JOIN ttt3.User u2 "
					+ " ON u2.Id = g1.Player2"
					+ " Where g1.isFull = 'N'";
			
			ps = conn.prepareStatement(query);
			rs = ps.executeQuery();

			while (rs.next()) {
				GameRoom room = new GameRoom();
				
				room.setGameId(rs.getLong("gameId"));
				
				User player1 = new User();
				player1.setUserId(rs.getLong("palyer1_id"));
				player1.setName(rs.getString("palyer1_name"));
				player1.setUsername(rs.getString("palyer1_username"));
				
				User player2 = new User();
				player2.setUserId(rs.getLong("palyer2_id"));
				player2.setName(rs.getString("palyer2_name"));
				player2.setUsername(rs.getString("palyer2_username"));
				
				room.setPlayer1(player1);
				room.setPlayer2(player2);
				
				gameRooms.add(room);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			/*try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}*/
			
		}
		
		return gameRooms;

	}
}
